import pandas as pd
import numpy as np
from pandas import DataFrame


# excel,xls,csv等文件导入导出
# 可能需要 pip install xlrd   &   pip install xlwt

# df=DataFrame(pd.read_excel("E:/WeChat Files/WeChat Files/JJ96001/FileStorage/File/2019-11/武汉项目软件问题汇总.xls"))
# print(df)
# df.to_excel("C:/Users/Administrator/Desktop/testPandasExcel.xls")

dataFrameEg=DataFrame({"languageScore":[60,30,65],"mathScore":[93,96,92],"englishScore":[90,77,88]},index=["张飞","关羽","黄忠"])

# ---------------------------删除列 删除行-------------------------------
df=dataFrameEg.drop(columns=["englishScore"])
df2=dataFrameEg.drop(index="张飞")

# ---------------------------重命名-------------------------------
#重命名,inplace是否替换原对象，默认试False
dataFrameEg.rename(columns={"englishScore":"英语分"},inplace=True)
df3=dataFrameEg.rename(index={"张飞":"zhangfei"})


# ---------------------------去重-------------------------------
# drop_duplicates(subset=None, keep='first', inplace=False)
# keep : {‘first’, ‘last’, False}, default ‘first’  删除重复项并保留第一次出现的项  ,last 最后一次，False 删除所有重复项
df3["languageScore"]["zhangfei"]=65
dfDistinct=df3.drop_duplicates('languageScore',"first")     #保留第一次出现65的地方
print(dfDistinct)


# ---------------------------格式-------------------------------
# 改变数据格式
df4=df3["languageScore"].astype("str")
df3['languageScore'].astype(np.int64)

# 数据间的空格,直接用python str的strip方法，eg: rstrip,lstrip,strip,strip('$')   可以是任何符号

#大小写转换，直接用python的upper，lower,title(首字母大写)
print("aaaa".title())

# ---------------------------空值查找-------------------------------
df4=DataFrame({"col1":[None,3],"col2":[1,2]})
judge=df4.isnull()
print(judge)
#     col1   col2
# 0   True  False
# 1  False  False

print(df4.isnull().any())     #判断哪一列存在空值
# col1     True
# col2    False
# dtype: bool


# --------------------------数据清洗----------------------------------
# apply
df5=DataFrame({"col1":["我是col1 a","我是 col1 b"],"col2":["我是col2 a","我是 col2 b"]})
df5["col1"]=df5["col1"].apply(str.upper)
print(df5)
   #添加一列sum
def addSumCol(srs):
    srs['sum']=srs['languageScore']+srs['mathScore']+srs['英语分']
    return srs
df3=df3.apply(addSumCol,axis=1)
print(df3)
    #多参数，增加和的倍数
def  sumMultiple(srs,n,m):
    print("--------------------------------------")
    print(srs)
    srs['sum2']=(srs['languageScore']+srs['mathScore']+srs['英语分'] )*n
    srs['sum3']=(srs['languageScore']+srs['mathScore']+srs['英语分'] )*m
    return srs
df3=df3.apply(sumMultiple,axis=1,args=(2,3))
print(df3)    

# -----------------------------统计函数-----------------------------
print("\n各课成绩各项统计数据:\n",df3.describe())
print("\n各课成绩最小值:\n",df3.min())
print("\n各课成绩最大值:\n",df3.max())
print("\n各课成绩和:\n",df3.sum())
print("\n各课成绩平均值:\n",df3.mean())
print("\n各课成绩中位数:\n",df3.median())
print("\n各课成绩方差:\n",df3.var())
print("\n各课成绩标准差:\n",df3.std())
print("\n索引成绩最大值值索引位:\n",pd.Index(df3).argmax())        # 5
print("\n索引各科成绩最大值值索引位:\n",df3.idxmax())               
# idxmain()..

# ------------------------------数据表合并  内连、左连、右连、外连-----------------------------
df6=DataFrame({"languageScore":[60,31,65],"mathScore":[93,96,92],"englishScore":[90,77,88],"name":["张飞","关羽","黄忠"]})
df7=DataFrame({"languageScore":[60,31,68],"mathScore":[93,96,92],"aaaaaaaaaaaaaaaa":[90,77,88],"name":["张飞1","关羽","黄忠"]})
    
    #基于指定列合并,指定列值相同的项保留,默认inner内连接
df8=pd.merge(df6,df7,on="name")
print(df8)
"""
   languageScore_x  mathScore_x  englishScore name  languageScore_y  mathScore_y  aaaaaaaaaaaaaaaa
0               30           96            77   关羽               30           96                77
1               65           92            88   黄忠               65           92                88
"""
df9=pd.merge(df6,df7,how="inner",on="name")  #默认：列值 行值都相同交集,同样可以指定列
print(df9)                  
"""
   languageScore  mathScore  englishScore name  aaaaaaaaaaaaaaaa
0             31         96            77   关羽                77
"""

df10=pd.merge(df6,df7,how="left")      #right同理
print(df10)
"""
   languageScore  mathScore  englishScore name  aaaaaaaaaaaaaaaa
0             60         93            90   张飞               NaN
1             31         96            77   关羽              77.0
2             65         92            88   黄忠               NaN
"""

df11=pd.merge(df6,df7,how="outer")
print(df11)